<?php
/**
 *
 * MYSQL处理,连接MYSQL,取得,修改,添加,删除数据
 *
 *
 * This is NOT a freeware, use is subject to license terms
 *
 * @copyright Copyright (C) 2005 - 2099 Cenwor Inc.
 * @license http://www.cenwor.com
 * @link http://www.jishigou.net
 * @author 狐狸<foxis@qq.com>
 * @version $Id: mysql.db.php 2862 2013-02-28 03:03:27Z wuliyong $
 * @todo 增加SQL安全过滤 2012年2月2日
 * @todo 支持主从数据库的查询更新操作 2012年9月14日
 * @todo 内容整理，清简
 */

if(!defined('IN_JISHIGOU'))
{
    exit('invalid request');
}

define("QUERY_SAFE", true);

class MySqlHandler extends DatabaseHandler 
{
	var $TableName; 	var $FieldList; 
	
	var $Charset='gbk';
	
	var $Links = array();
	var $CurLink = '';
	var $slave_id = null;
	var $cur_db = '';

	
	function MySqlHandler($server_host='', $server_port = '3306') {
		$this->DatabaseHandler($server_host, $server_port);
	}
	
		
	function DoConnect($db_user, $db_pass, $db_name, $db_pconnect = true) {	
		return $this->do_connect($this->ServerHost, $this->ServerPort, $db_user, $db_pass, $db_charset, $db_name, $db_pconnect);
	}
	
	function slave_connect($cmd) {
		global $_J;		
		if(is_array($_J["config"]["db_slave"]) && ($c = count($_J["config"]["db_slave"])) > 0) {
			$server_id = null;
						if("SELECT" == $cmd) { 				if(!isset($this->slave_id)) {
					$this->slave_id = array_rand($_J["config"]["db_slave"]);
				}
				$server_id = $this->slave_id;
			}
			$this->connect($server_id);
		}	
	}
	
	function connect($server_id = null) {
		global $_J;
		if(isset($server_id) && ($c = $_J["config"]["db_slave"][$server_id]) && $c["db_host"]) {
			$server_id = "db_slave_" . $server_id;			
			$db_host = $c["db_host"];
			$db_port = $c["db_port"];
			$db_user = $c["db_user"];
			$db_pass = $c["db_pass"];
			$db_charset = $c["db_charset"];
			$db_name = $c["db_name"];
			$db_pconnect = isset($c["db_persist"]) ? $c["db_persist"] : $c["db_pconnect"];
		} else {
			$server_id = 'db_master';
			$db_host = $_J['config']['db_host'];
			$db_port = $_J['config']['db_port'];
			$db_user = $_J['config']['db_user'];
			$db_pass = $_J['config']['db_pass'];
			$db_charset = $_J['config']['db_charset'];
			$db_name = $_J['config']['db_name'];
			$db_pconnect = isset($_J['config']['db_persist']) ? $_J['config']['db_persist'] : $_J['config']['db_pconnect'];
		}
		if(!$db_charset) {
			$db_charset = $_J['config']['charset'];
		}
		if(!isset($this->Links[$server_id])) {
			$this->Links[$server_id] = $this->do_connect($db_host, $db_port, $db_user, $db_pass, $db_charset, $db_name, $db_pconnect);
		}
		$_J['config']['current_db_name'] = $db_name;
		$this->cur_db = $db_host . '.' . $db_name;
		$this->CurLink = $this->Links[$server_id];
	}
	
	function do_connect($db_host, $db_port, $db_user, $db_pass, $db_charset, $db_name, $db_pconnect) {
		if(!$db_host || !$db_user || !$db_name) {
			exit('db config is empty');
		}
		if($db_port && false===strpos($db_host, ':')) {
			$db_host .= ':' . $db_port;
		}		
		if($db_pconnect) {
			$link = @mysql_pconnect($db_host, $db_user, $db_pass, MYSQL_CLIENT_COMPRESS); 
		} else {
			$link = @mysql_connect($db_host, $db_user, $db_pass, 1, MYSQL_CLIENT_COMPRESS);
		}
		if(!$link) {
			exit(mysql_errno() . ':' . mysql_error());
		} else {
			$this->CurLink = $link;
			if($this->GetVersion() > '4.1') {
				$dbcharset = $db_charset ? $this->Charset($db_charset) : $this->Charset;
				$serverset = $dbcharset ? 'character_set_connection='.$dbcharset.', character_set_results='.$dbcharset.', character_set_client=binary' : '';
				$serverset .= $this->GetVersion() > '5.0.1' ? ((empty($serverset) ? '' : ',').'sql_mode=\'\'') : '';
				$serverset && mysql_query("SET $serverset", $link);
			}
			$db_name && @mysql_select_db($db_name, $link);
		}
		return $link;
	}
	
	
	function Charset($charset) {
		$this->Charset = ((false!==strpos($charset,'-')) ? str_replace("-", "", strtolower($charset)) : $charset);		
		return $this->Charset;
	}
	
	

	function Query($sql,$type='')
	{
		$this->CheckQuery($sql);
		
		if(true === DEBUG) {
			$debug_list = debug_backtrace();
			foreach($debug_list as $key => $debug) {
								if($debug["file"] != __FILE__ and basename($debug["function"]) != "_execute" && 
					!in_array(basename($debug["file"]), array("table.class.php", ))) {
					if(in_array($debug["class"], array(__CLASS__, "DB", "table", ))) {
						$file = $debug["file"];
						$line = $debug["line"];
					}
				}
			}
			unset($debug_list);
			$query_start_time = microtime(true);
		}
		$func=$type==='UNBUFFERED'?'mysql_unbuffered_query':'mysql_query';
		$result = $func($sql, $this->CurLink);
		if($result==false) {
			if(in_array($this->GetLastErrorNo(), array(2006, 2013)) && substr($type, 0, 5) != 'RETRY') {
				$this->CloseConnection();
				$this->connect();
				$result = $this->Query($sql, 'RETRY'.$type);
			} elseif (in_array($this->GetLastErrorNo(), array(1040)) && substr($type,0,4) != "WAIT" && substr($type,0,5) < "WAIT3") {
				usleep(100000 * max(1,min(6,2 * ((int) substr($type,4,1) + 1))));

				static $WAITTIMES = 0;
				$result = $this->Query($sql, 'WAIT'.++$WAITTIMES.$type);
			} elseif (($type != 'SKIP_ERROR' && 'SILENT' != $type && substr($type, 5) != 'SKIP_ERROR')) {
				if('admin' === MEMBER_ROLE_TYPE || true === JISHIGOU_FOUNDER || true === IN_JISHIGOU_UPGRADE) {
					exit($this->GetLastError($sql, $file, $line)); 				}
				return false;
							} else {
				return false;
			}
		}

		if(true===DEBUG && $this->GetQueryCount() < 100) {
			$query_execute_time = (round(microtime(true) - $query_start_time, 5));
			
									$explain="";
			if (substr(trim(strtoupper($sql)),0,6)=="SELECT") {
				$explain_id = mysql_query("EXPLAIN $sql", $this->CurLink);
				while(false != ($array=mysql_fetch_array($explain_id))) {
					if(!empty($explain)) $explain .="<hr>";
					$explain .= "<div" . (($query_execute_time >= 0.3 || ($array[rows] > 1000 && empty($array[key]))) ? 
						" style=\"background:green; color:white;\"" : 
						"") . ">
					Sql_Table: $array[table]<br />
					" . ($array[type] ? "Query_type: $array[type]<br />" : "") . "
					" . ($array[possible_keys] ? "possible_keys: $array[possible_keys]<br />" : "") . "
					" . ($array[key] ? "<font color=red>Query_key: $array[key]</font><br />" : "") . "
					" . ($array[rows] ? "<font color=red>Query_rows: <b>$array[rows]</b></font><br />" : "") . "
					" . ($query_execute_time ? "<font color=red>Query_time: <b>$query_execute_time</b></font><br />" : "") . "
					" . ($array[key_len] ? "key_len: $array[key_len]<br />" : "") . "
					" . ($array[ref] ? "ref: $array[ref]<br />" : "") . "
					" . ($array[Extra] ? "extra: $array[Extra]<br />" : "") . "
				  </div>";
				}
			}
			if($GLOBALS["_J"]["config"]["db_slave"] && $this->cur_db) {
				$explain = "cur_db: {$this->cur_db}<br />" . $explain;
			}

			$this->SetSqlStore(array("SQL" => $sql, "TIME" => $query_execute_time, "FILE" => $file, "LINE" => $line, "FROM" => "Database", "explain" => $explain));
		}

		return new MySqlIterator($result);
	}
	function CheckQuery($sql) {		
		static $status = null, $checkcmd = array('SELECT', 'UPDATE', 'INSERT', 'REPLACE', 'DELETE'), $static_query_safes = array();		
		
		if($status === null) {
			$status = QUERY_SAFE;
		}
		if($status) {
			$cmd = trim(strtoupper(substr($sql, 0, strpos($sql, ' '))));
			if(in_array($cmd, $checkcmd)) {	
				
							
				$cache_id = md5($sql);
				if(false==($test = $static_query_safes[$cache_id])) {
					if(isset($GLOBALS['_J']['query_safes'][$cache_id]) && $GLOBALS['_J']['query_safes'][$cache_id] == md5($cache_id . $GLOBALS['_J']['config']['auth_key'])) {
						$test = 1;
					} else {
						$test = $this->_do_query_safe($sql);
					}
					
					$static_query_safes[$cache_id] = $test;
				}
				if($test < 1) exit();
			}
		}
		return true;
	}

	
	function _do_query_safe($sql) {
		static $_CONFIG = null;
		
		if($_CONFIG === null) {
			$_config = array();
			$_config['security']['querysafe']['status'] = 1;
			$_config['security']['querysafe']['dfunction']['0'] = 'load_file';
			$_config['security']['querysafe']['dfunction']['1'] = 'hex';
			$_config['security']['querysafe']['dfunction']['2'] = 'substring';
 			$_config['security']['querysafe']['dfunction']['3'] = 'substr';
			$_config['security']['querysafe']['dfunction']['4'] = 'ord';
			$_config['security']['querysafe']['dfunction']['5'] = 'char';
			$_config['security']['querysafe']['daction']['0'] = 'intooutfile';
			$_config['security']['querysafe']['daction']['1'] = 'intodumpfile';
			$_config['security']['querysafe']['daction']['2'] = 'unionselect';
			$_config['security']['querysafe']['daction']['4'] = 'unionall';
			$_config['security']['querysafe']['daction']['5'] = 'uniondistinct';
			$_config['security']['querysafe']['dnote']['0'] = '/'.'*';
			$_config['security']['querysafe']['dnote']['1'] = '*/';
			$_config['security']['querysafe']['dnote']['2'] = '#';
			$_config['security']['querysafe']['dnote']['3'] = '--';
			$_config['security']['querysafe']['dlikehex'] = 1;
			$_config['security']['querysafe']['afullnote'] = 1;
			$_CONFIG = $_config['security']['querysafe'];
		}
		

		$sql = str_replace(array('\\\\', '\\\'', '\\"', '\'\''), '', $sql);
		$mark = $clean = '';
		if(strpos($sql, '/') === false && strpos($sql, '#') === false && strpos($sql, '-- ') === false) {
			$clean = preg_replace("/'(.+?)'/s", '', $sql);
		} else {
			$len = strlen($sql);
			$mark = $clean = '';
			for ($i = 0; $i <$len; $i++) {
				$str = $sql[$i];
				switch ($str) {
					case '\'':
						if(!$mark) {
							$mark = '\'';
							$clean .= $str;
						} elseif ($mark == '\'') {
							$mark = '';
						}
						break;
					case '/':
						if(empty($mark) && $sql[$i+1] == '*') {
							$mark = '/'.'*';
							$clean .= $mark;
							$i++;
						} elseif($mark == '/'.'*' && $sql[$i -1] == '*') {
							$mark = '';
							$clean .= '*';
						}
						break;
					case '#':
						if(empty($mark)) {
							$mark = $str;
							$clean .= $str;
						}
						break;
					case "\n":
						if($mark == '#' || $mark == '--') {
							$mark = '';
						}
						break;
					case '-':
						if(empty($mark)&& substr($sql, $i, 3) == '-- ') {
							$mark = '-- ';
							$clean .= $mark;
						}
						break;

					default:

						break;
				}
				$clean .= $mark ? '' : $str;
			}
		}

		$clean = preg_replace("/[^a-z0-9_\-\(\)#\*\/\"]+/is", "", strtolower($clean));

		if($_CONFIG['afullnote']) {
			$clean = str_replace('/'.'*'.'*/','',$clean);
		}

		if(is_array($_CONFIG['dfunction'])) {
			foreach($_CONFIG['dfunction'] as $fun) {
				if(strpos($clean, $fun.'(') !== false) return '-1';
			}
		}

		if(is_array($_CONFIG['daction'])) {
			foreach($_CONFIG['daction'] as $action) {
				if(strpos($clean,$action) !== false) return '-3';
			}
		}

		if($_CONFIG['dlikehex'] && strpos($clean, 'like0x')) {
			return '-2';
		}

		if(is_array($_CONFIG['dnote'])) {
			foreach($_CONFIG['dnote'] as $note) {
				if(strpos($clean,$note) !== false) {
					return '-4';
				}
			}
		}

		return 1;
	}
	
	function FetchAll($sql, $keyfield='') {
		$list = false;
		$query = $this->Query($sql);
		if($query) {
			$list = array();
			while (false != ($row = $query->GetRow())) {
				if($keyfield && isset($row[$keyfield])) {
					$list[$row[$keyfield]] = $row;
				} else {
					$list[] = $row;
				}
			}
			$query->FreeResult();
		}
		return $list;
	}
	
    function fetch_first($sql) {
        return $this->FetchFirst($sql);
    }
    function FetchFirst($sql) {
        $ret = array();
        $query = $this->Query($sql);
        if($query) {
        	$ret = $query->GetRow();
        	$query->FreeResult();
        }
        return $ret;
    }
    function ResultFirst($sql) {
        $ret = '';
        $query = $this->Query($sql);
        if($query) {
            $ret = $query->result(0);
            $query->FreeResult();
        }
        return $ret;
    }
	function BuildIn($mixed,$name=null)
	{
		if($name === NULL)$name = $this->FieldList['PRI'];
		$type=gettype($mixed);
		if($type=="string" or $type=="integer" or $type=="double")
		{
			$mixed=trim($mixed,',');
			$mixed=strpos($mixed,',')!==false
					?"'".str_replace(',',"','",$mixed)."'"
					:"'$mixed'";
		}
		elseif($type=="array")
		{
			$mixed=!empty($mixed)?"'".implode("','",array_unique($mixed))."'":'null';
		}

		Return $name!=null?"$name IN ($mixed)":$mixed;
	}

	
	function GetVersion()
	{
		return mysql_get_server_info($this->CurLink);
	}

	
	function GetLastError($sql, $file, $line)
	{
		$error = mysql_error($this->CurLink);

		return $error . $sql;
	}
	function GetLastErrorString()
	{
		return mysql_error($this->CurLink);
	}
	function GetLastErrorNo()
	{
		return mysql_errno($this->CurLink);
	}

	
	function Insert_ID()
	{
		return ($id = mysql_insert_id($this->CurLink)) >= 0 ? $id : $this->ResultFirst("SELECT last_insert_id()");
	}

	function LastInsertId()
	{
		return $this->Insert_ID();
	}

	
	function AffectedRows()
	{
		return mysql_affected_rows($this->CurLink);
	}

	
	function CloseConnection() {
		$ret = mysql_close($this->CurLink);
		if($this->Links) {
			foreach($this->Links as $link) {
				$ret = mysql_close($link);
			}
		}
		$this->CurLink = null;
		$this->Links = array();
		return $ret;
	}
	
}



class MySqlIterator
{
	
	var $_resource_id;

	
	var $_current_row;

	
	var $_total_rows;

	
	function MySqlIterator($resource_id)
	{
		$this->_resource_id = $resource_id;
		$this->_total_rows = 0;
		$this->_current_row = 0;
	}

	
	function GetNumRows()
	{
		$this->_total_rows = mysql_num_rows($this->GetResourceId());

		return $this->_total_rows;
	}

	function GetNumFields()
	{
		return mysql_num_fields($this->GetResourceId());
	}

	
	function GetResourceId()
	{
		return $this->_resource_id;
	}

	
	function GetCurrentRow()
	{
		return $this->_current_row;
	}

	
	function isSuccess()
	{
		return $this->GetResourceId() ? true : false;
	}

	
	function FreeResult() {
		return mysql_free_result($this->GetResourceId());
	}

	
	function GetRow($result_type = 'assoc') {
		$this->_current_row++;

		switch($result_type) {
			case 'assoc':
				return mysql_fetch_assoc($this->GetResourceId());
				break;
			case 'row':
				return mysql_fetch_row($this->GetResourceId());
				break;
			case 'both':
				return mysql_fetch_array($this->GetResourceId());
				break;
			case 'object':
				return mysql_fetch_object($this->GetResourceId());
				break;
		}
	}
	function result($row)
	{
		return @mysql_result($this->GetResourceId(),$row);
	}

	
	function GetAll($result_type = 'assoc') {
		$list = array();
		while(false != ($row = $this->GetRow($result_type))) {
			$list[] = $row;
		}
		$this->FreeResult();
		return $list;
	}
}

?>